import java.io.FileNotFoundException;
import java.io.FileWriter;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map.Entry;

import org.apache.commons.lang3.StringUtils;
//import org.apache.log4j.Logger;
import org.apache.log4j.PropertyConfigurator;

import com.csvreader.CsvReader;
import com.csvreader.CsvWriter;

public class Test {
	private static final double PRECISION = 1000000;
	static int INDEX = 0;
	static int QUERY_INDEX = 0;
	static String EXPORT_FILE = "export5_test.csv";
	static String VOCABULARY_FILE = "vocabulary.csv";
	static String VECTOR_FILE = "vector.csv";
	static String WEKA_FILE = "weka.csv";
	static String RESULT_FILE = "result.csv";
		
	public static void main(String[] args) {
		PropertyConfigurator.configure("log4j.properties");
		//Logger logger = Logger.getLogger(Test.class);
		HashMap<String, String> vocabulary = new HashMap<String, String>();
		Parser parser = new Parser();
		
		CsvReader queries_file;
		CsvWriter result_file;
		CsvWriter vocabulary_file;
		CsvWriter vector_file;
		//CsvWriter weka_file;
		
		//int [][] a = new int [1520][1800];
		
		try {
			queries_file = new CsvReader(EXPORT_FILE);
			result_file = new CsvWriter(new FileWriter(RESULT_FILE, false), ',');
			vocabulary_file = new CsvWriter(new FileWriter(VOCABULARY_FILE, false), ',');
			vector_file = new CsvWriter(new FileWriter(VECTOR_FILE, false), ';');
			//weka_file = new CsvWriter(new FileWriter(WEKA_FILE, true), ' ');
			
			queries_file.readHeaders();
			
			HashSet<QueryStatistics> statSet = new HashSet<QueryStatistics>();

			while (queries_file.readRecord())
			{
				String query = queries_file.get("SQL_TEXT") + ";";
				query = parser.deleteComments(query);
				query = parser.replaceLiterals(query);
				query = parser.trim(query);
				query = query.toUpperCase().trim();
				
				int executionNum = new Integer(queries_file.get("EXECUTIONS")).intValue();
				long bufferGets = new Long(queries_file.get("BUFFER_GETS")).longValue();
				long diskReads = new Long(queries_file.get("DISK_READS")).longValue();
				int commandType = new Integer(queries_file.get("COMMAND_TYPE")).intValue();
				double elapsedTime = new Double(queries_file.get("ELAPSED_TIME"))/PRECISION;
				
				QueryStatistics queryStatistics = new QueryStatistics(query, diskReads, bufferGets, elapsedTime, commandType, executionNum);
				statSet.add(queryStatistics);}
			
			for(QueryStatistics queryStat : statSet)
			{
				ArrayList<String> result = new ArrayList<String>();
				String vector = new String("[");
				String query = queryStat.getSqlText();
				System.out.println(query);
				
				QUERY_INDEX++;				
				
				Query resQuery = new Query(query);
				
				for(String subquery : parser.parseWithAndUnionToken(query))
				{
					if(!subquery.endsWith(";"))
						subquery += ";";
					/**
					 * handle case:
					 * SELECT (select so_id from trees where rownum < 2) AS object_id, 
					 * s.object_id AS value 
					 * FROM nc_objects s 
					 */
					System.out.println(subquery);					
					resQuery = parser.parseSelectToken(subquery, resQuery);
					result.addAll(resQuery.getResults());
					System.out.println(result.toString());
					System.out.println(resQuery.getSubqueries().toString());
					result.addAll(parser.parseFromToken(subquery));
					System.out.println(result.toString());
					result.addAll(parser.parseWhereToken(subquery));
					System.out.println(result.toString());
					result.addAll(parser.parseHierarhyToken(subquery));
					System.out.println(result.toString());
					result.addAll(parser.parseGroupByToken(subquery));
					System.out.println(result.toString());
					result = parser.removeDuplicates(result);
					System.out.println(result.toString());
				}				
				
				System.out.println("resQuery:" + resQuery.getSubqueries().toString());
				System.out.println("result:" + result.toString());
				
				if(result.size()==0)
					continue;
				
				for(String token : parser.removeAliases(result))
				{
					if(StringUtils.isEmpty(token) || StringUtils.equals(token, "@NUMBER") || StringUtils.equals(token, "@LITERAL") || StringUtils.equals(token, "@BINDING")) continue;
					
					result_file.write(new Integer(QUERY_INDEX).toString());
					result_file.write(token);
					result_file.endRecord();
					
					if(vocabulary.get(token) == null)
					{
						vocabulary.put(token, new Integer(INDEX).toString());
						INDEX++;
					}
					vector += vocabulary.get(token) + ",";
					//a[QUERY_INDEX-1][new Integer(vocabulary.get(token)).intValue()] = 1;
				}
				vector = vector.substring(0, vector.length()-1) + "]";
				vector_file.write(new Integer(QUERY_INDEX).toString());
				vector_file.write(vector);
				//vector_file.write(new Double(queryStat.getElapsedTime()).toString());
				//vector_file.write(new Long(queryStat.getBufferGets()).toString());
				//vector_file.write(new Long(queryStat.getDiskReads()).toString());
				//vector_file.write(new Integer(queryStat.getExecutions()).toString());
				DecimalFormat df = new DecimalFormat("#.#################");
				vector_file.write(df.format(queryStat.getK()));
				vector_file.endRecord();
			}			
			
			for(Entry<String, String> entry : vocabulary.entrySet())
			{
				//vocabulary_file.write(entry.getValue());
				vocabulary_file.write(entry.getKey());
				vocabulary_file.endRecord();
			}
			
			/*for(int i = 0; i<1520; i++)
			{
				for(int j = 0; j<1800; j++)
				{
					weka_file.write(new Integer(a[i][j]).toString());
				}
				weka_file.endRecord();
			}*/
			
			queries_file.close();
			vocabulary_file.close();
			vector_file.close();
			result_file.close();
			//weka_file.close();
			
			System.out.println(INDEX);
			
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}		
	}	
}
